package com.bootdo.staticmask.core.oracle;

import java.io.Closeable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.bootdo.staticmask.core.bean.ColumnProperty;
import com.bootdo.staticmask.domain.MaskOracleDO;

/**
 * 1.Oracle JDBC Thin using a ServiceName:
 * jdbc:oracle:thin:@//<host>:<port>/<service_name> Example:
 * jdbc:oracle:thin:@//192.168.2.1:1521/XE 2.Oracle JDBC Thin using an SID:
 * jdbc:oracle:thin:@<host>:<port>:<SID> Example:
 * jdbc:oracle:thin:@192.168.2.1:1521:X01A 3.Oracle JDBC Thin using a TNSName:
 * jdbc:oracle:thin:@<TNSName> Example: jdbc:oracle:thin:@GL
 *
 * 使用时手动创建对象，不使用自动注入
 */

public class OracleDataSource implements Closeable, AutoCloseable{

	private static final Logger LOGGER = LoggerFactory.getLogger(OracleDataSource.class);

	private static final int SID = 1;
	private static final int SERVICE_NAME = 0;

	private static final String SERVICE_CONN = "jdbc:oracle:thin:@//%s:%s/%s";
	private static final String SID_CONN = "jdbc:oracle:thin:@%s:%s:%s";
	private static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
	
	private static final String TABLE_NAME = "TABLE_NAME";
	private static final String OWNER = "OWNER";
	private static final String COMMENTS = "COMMENTS";
	private static final String COLUMN_NAME = "COLUMN_NAME";
	private static final String DATA_TYPE = "DATA_TYPE";

	private Connection connection;

	// 返回用户能够查看的所有表（不包括系统用户下的表）
	private static final String SQL_SELECT_TABLE_NAME = "SELECT A.OWNER, A.TABLE_NAME, B.COMMENTS FROM ALL_TABLES A INNER JOIN ALL_TAB_COMMENTS B" + 
			" ON A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME" + 
			" WHERE A.OWNER NOT IN ('ANONYMOUS', 'EXFSYS','MDDATA', 'MGMT_VIEW', 'OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMATN_SCHEMA','CTXSYS', 'DBSNMP', 'INTERNAL', 'MDSYS', 'MTSSYS', 'LBACSYS', 'ODM', 'SYS', 'SYSMAN', 'SYSTEM', 'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'ORDDATA', 'APPQOSSYS', 'APEX_030200', 'MGMT_VIEW', 'FLOWS_FILES', 'OWBSYS_AUDIT','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'OWBSYS', 'OWBSYS_AUDIT')"; 
	
	// 查找指定表用户，表名，列名，数据类型，列注释
	private static final String SQL_SHOW_COLUMNS = "SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, B.COMMENTS " + 
			"FROM ALL_TAB_COLUMNS A INNER JOIN ALL_COL_COMMENTS B ON A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME WHERE A.TABLE_NAME = '%s' AND A.OWNER = '%s' ORDER BY A.COLUMN_ID";
	
	// 查找表的所有数据
	private static final String SQL_SELECT_TABLE = "SELECT * FROM %s";
	
	static {
		try {
			Class.forName(DRIVER_NAME);
		} catch (ClassNotFoundException e) {
			LOGGER.error(e.getMessage(), e);
			System.exit(-1);
		}
	}

	public OracleDataSource(MaskOracleDO maskOracleDO) {
		String url = null;
		if (maskOracleDO.getServicenameIs() == SID) {
			url = String.format(SID_CONN, maskOracleDO.getOracleIp(), maskOracleDO.getOraclePort(),
					maskOracleDO.getOracleServicename());
		} else if (maskOracleDO.getServicenameIs() == SERVICE_NAME) {
			url = String.format(SERVICE_CONN, maskOracleDO.getOracleIp(), maskOracleDO.getOraclePort(),
					maskOracleDO.getOracleServicename());
		}

		try {
			this.connection = DriverManager.getConnection(url, maskOracleDO.getOracleUser() + " as sysdba",
					maskOracleDO.getOraclePassword());
		} catch (Exception e) {
			LOGGER.error(e.getMessage(), e);
		}
	}

	/**
	 * 获取登陆用户所有表
	 * 
	 * @return 用户名，表名，注释
	 */
	public List<Map<String, String>> getTableNames() {
		List<Map<String, String>> tableList = null;
		try (PreparedStatement preparedStatement = connection.prepareStatement(SQL_SELECT_TABLE_NAME);
				ResultSet resultSet = preparedStatement.executeQuery()) {
			tableList = new ArrayList<>();
			while (resultSet.next()) {
				Map<String, String> tableInfo = new HashMap<>();
				tableInfo.put("name", resultSet.getString(OWNER) + "." + resultSet.getString(TABLE_NAME));
				String comment = resultSet.getString(COMMENTS);
				if (comment == null) {
					tableInfo.put("comment", "");
				}else {
					tableInfo.put("comment", comment);
				}
				tableList.add(tableInfo);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage(), e);
		}

		return tableList;
	}

	/**
	 * 获取某张表下所有列
	 * 
	 * @param tableName
	 * @return
	 */
	public List<ColumnProperty> getTableColumns(String owner, String tableName){

		List<ColumnProperty> columns = null;
		String sqlColumn = String.format(SQL_SHOW_COLUMNS, tableName, owner);
		try (PreparedStatement preparedStatement = connection.prepareStatement(sqlColumn);
				ResultSet resultSet = preparedStatement.executeQuery();) {
			columns = new ArrayList<>();
			while (resultSet.next()) {
				ColumnProperty columnProperty = new ColumnProperty();
				columnProperty.setColumnName(resultSet.getString(COLUMN_NAME));
				columnProperty.setColumnDataType(resultSet.getString(DATA_TYPE));
				columnProperty.setColumnComment(resultSet.getString(COMMENTS));
				columns.add(columnProperty);
			}

		} catch (Exception e) {
			LOGGER.error(e.getMessage(), e);
		}
		return columns;
	}

	/**
	 * 连接测试
	 * 
	 * @param maskOracleDO
	 * @throws Exception
	 */
	public static String testConnection(MaskOracleDO maskOracleDO) {
		String url;

		if (maskOracleDO.getServicenameIs() == SID) {
			url = String.format(SID_CONN, maskOracleDO.getOracleIp(), maskOracleDO.getOraclePort(),
					maskOracleDO.getOracleServicename());
		} else if (maskOracleDO.getServicenameIs() == SERVICE_NAME) {
			url = String.format(SERVICE_CONN, maskOracleDO.getOracleIp(), maskOracleDO.getOraclePort(),
					maskOracleDO.getOracleServicename());
		} else {
			throw new IllegalArgumentException("servicenameIs: " + maskOracleDO.getServicenameIs());
		}

		String result = "success";

		try {
			DriverManager.getConnection(url, maskOracleDO.getOracleUser(), maskOracleDO.getOraclePassword()).close();
		} catch (SQLException e) {
			result = e.getLocalizedMessage();
		}

		return result;
	}

	/**
	 * 关闭连接
	 */
	public void closeConnection() {
		try {
			connection.close();
		} catch (SQLException e) {
			LOGGER.error(e.getMessage(), e);
		}
	}

	public ResultSet getResultSet(String tableName) {
		ResultSet resultSet = null;
		String sql = String.format(SQL_SELECT_TABLE, tableName);
		try {
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
		} catch (SQLException e) {
			LOGGER.error(e.getMessage(), e);
		}
		return resultSet;
	}

	public static void main(String[] args) {
		MaskOracleDO maskOracleDO = new MaskOracleDO();
		maskOracleDO.setOracleIp("192.168.188.151");
		maskOracleDO.setOracleUser("scott");
		maskOracleDO.setOraclePassword("tiger");
		maskOracleDO.setOraclePort("1521");
		maskOracleDO.setServicenameIs(SERVICE_NAME);
		maskOracleDO.setOracleServicename("DBONE");

		OracleDataSource oralDataSourceDao = new OracleDataSource(maskOracleDO);
		System.out.println(oralDataSourceDao.getTableNames());
		oralDataSourceDao.closeConnection();

	}

	@Override
	public void close() {
		closeConnection();
	}
}
